package com.ruoyi.service.jpjs.rlexportdata.impl;

import com.ruoyi.mapper.jpjs.PublicMapper;
import com.ruoyi.mapper.jpjs.czlrfy.CzlrfyMapper;
import com.ruoyi.mapper.jpjs.empcertificate.EmpCertificateMapper;
import com.ruoyi.mapper.jpjs.humanresources.HumanResourcesMapper;
import com.ruoyi.service.jpjs.rlexportdata.RlExportDataThreeService;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author zeo
 * @explain XXX
 * @date 2020/6/11 10:03
 */
@Service
public class RlExportDataThreeServerImpl implements RlExportDataThreeService {

    @Autowired
    PublicMapper publicMapper;

    @Autowired
    HumanResourcesMapper humanResourcesMapper;

    @Autowired
    CzlrfyMapper czlrfyMapper;

    @Autowired
    EmpCertificateMapper empCertificateMapper;

    @Override
    public void exportHumanResources(HSSFWorkbook wb, String years, String months, List<Map<String, Object>> maps) {
        HSSFSheet sheet = wb.createSheet("人力资源成本分析");
        HSSFRow row = sheet.createRow(0);
        sheet.setDefaultRowHeightInPoints(10);
        //合并单元格
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 1, 16));
        HSSFCellStyle txtStyle = wb.createCellStyle();
        HSSFFont txtFont = wb.createFont();
        txtFont.setFontName("宋体");
        txtFont.setFontHeightInPoints((short) 20);
        txtFont.setBold(true);
        txtStyle.setFont(txtFont);
        row.createCell(1).setCellValue("金鹏建设集团  " + years + "  年 " + months + "  月人力资源统计月报表");
        for (Cell cell : row) {
            cell.setCellStyle(txtStyle);
        }

        row = sheet.createRow(1);
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 16));
        HSSFCellStyle txtStyle1 = wb.createCellStyle();
        HSSFFont txtFont1 = wb.createFont();
        txtFont1.setFontName("宋体");
        txtFont1.setFontHeightInPoints((short) 16);
        txtFont1.setBold(true);
        txtStyle1.setFont(txtFont1);
        txtStyle1.setAlignment(HorizontalAlignment.CENTER);
        row.createCell(0).setCellValue("七、人力资源成本分析");
        row.createCell(1);
        row.createCell(2);
        row.createCell(3);
        row.createCell(4);
        row.createCell(5);
        row.createCell(6);
        row.createCell(7);
        row.createCell(8);
        row.createCell(9);
        row.createCell(10);
        row.createCell(11);
        row.createCell(12);
        row.createCell(13);
        row.createCell(14);
        row.createCell(15);
        row.createCell(16);
        txtStyle1.setBorderBottom(BorderStyle.THIN);
        txtStyle1.setBorderLeft(BorderStyle.THIN);
        txtStyle1.setBorderTop(BorderStyle.THIN);
        txtStyle1.setBorderRight(BorderStyle.THIN);
        for (Cell cell : row) {
            cell.setCellStyle(txtStyle1);
        }

        row = sheet.createRow(2);
        HSSFCellStyle txtStyle2 = wb.createCellStyle();
        HSSFFont txtFont2 = wb.createFont();
        txtFont2.setFontName("宋体");
        txtFont2.setFontHeightInPoints((short) 11);
        txtStyle2.setFont(txtFont2);
        txtStyle2.setAlignment(HorizontalAlignment.CENTER);
        txtStyle2.setVerticalAlignment(VerticalAlignment.CENTER);
        sheet.addMergedRegion(new CellRangeAddress(2, 3, 0, 0));
        sheet.addMergedRegion(new CellRangeAddress(2, 2, 1, 4));
        sheet.addMergedRegion(new CellRangeAddress(2, 2, 5, 7));
        sheet.addMergedRegion(new CellRangeAddress(2, 2, 8, 10));
        sheet.addMergedRegion(new CellRangeAddress(2, 2, 11, 13));
        sheet.addMergedRegion(new CellRangeAddress(2, 2, 14, 16));

        row.createCell(0).setCellValue("单位");
        row.createCell(1).setCellValue("工资支出(含社保、公积金)");
        row.createCell(2);
        row.createCell(3);
        row.createCell(4);
        row.createCell(5).setCellValue("招聘支出");
        row.createCell(6);
        row.createCell(7);
        row.createCell(8).setCellValue("培训支出");
        row.createCell(9);
        row.createCell(10);
        row.createCell(11).setCellValue("其他活动或人资项目支出");
        row.createCell(12);
        row.createCell(13);
        row.createCell(14).setCellValue("人力资源总成本支出(1月——当月)");
        row.createCell(15);
        row.createCell(16);
        txtStyle2.setBorderBottom(BorderStyle.THIN);
        txtStyle2.setBorderLeft(BorderStyle.THIN);
        txtStyle2.setBorderTop(BorderStyle.THIN);
        txtStyle2.setBorderRight(BorderStyle.THIN);
        for (Cell cell : row) {
            cell.setCellStyle(txtStyle2);
        }

        row = sheet.createRow(3);
        row.createCell(0);
        row.createCell(1).setCellValue("总额(应发)");
        row.createCell(2).setCellValue("月末人数");
        row.createCell(3).setCellValue("人均工资");
        row.createCell(4).setCellValue("与上月比增加");
        row.createCell(5).setCellValue("总额");
        row.createCell(6).setCellValue("到岗人数");
        row.createCell(7).setCellValue("人均");
        row.createCell(8).setCellValue("总额");
        row.createCell(9).setCellValue("人次");
        row.createCell(10).setCellValue("人均");
        row.createCell(11).setCellValue("总额");
        row.createCell(12).setCellValue("人次");
        row.createCell(13).setCellValue("人均");
        row.createCell(14).setCellValue("总额");
        row.createCell(15).setCellValue("平均人数");
        row.createCell(16).setCellValue("人均成本");

        for (Cell cell : row) {
            cell.setCellStyle(txtStyle2);
        }

        /**
        * 后端拿取自身权限所拥有的工程公司
        */
        int i = 4;
        Map<String, Object> parameter = new HashMap<String, Object>();
        parameter.put("years", years);
        parameter.put("months", months);
        parameter.put("compList", maps);
        for (Map map : maps) {
            parameter.put("compId", map.get("compId"));
            List<Map<String, Object>> mapList = humanResourcesMapper.list(parameter);
            row = sheet.createRow(i);
            if (mapList.size() > 1) {
                Map<String, Object> map1 = mapList.get(0);
                row.createCell(0).setCellValue(map1.get("compName").toString());
                row.createCell(1).setCellValue(map1.get("totalWages").toString());
                row.createCell(2).setCellValue(map1.get("monthEndNum").toString());
                row.createCell(3).setCellValue(map1.get("averageWages").toString());
                row.createCell(4).setCellValue(map1.get("lastMonthComparison").toString());
                row.createCell(5).setCellValue(map1.get("totalRecruit").toString());
                row.createCell(6).setCellValue(map1.get("positionNum").toString());
                row.createCell(7).setCellValue(map1.get("averageRecruit").toString());
                row.createCell(8).setCellValue(map1.get("totalTrain").toString());
                row.createCell(9).setCellValue(map1.get("trainNum").toString());
                row.createCell(10).setCellValue(map1.get("averageTrain").toString());
                row.createCell(11).setCellValue(map1.get("totalOther").toString());
                row.createCell(12).setCellValue(map1.get("otherNum").toString());
                row.createCell(13).setCellValue(map1.get("averageOther").toString());
                row.createCell(14).setCellValue(map1.get("totalHuman").toString());
                row.createCell(15).setCellValue(map1.get("humanNum").toString());
                row.createCell(16).setCellValue(map1.get("averageHuman").toString());
            }else {
                row.createCell(0).setCellValue(publicMapper.findRoleName(map.get("compId").toString()));
                row.createCell(1).setCellValue(0);
                row.createCell(2).setCellValue(0);
                row.createCell(3).setCellValue(0);
                row.createCell(4).setCellValue(0);
                row.createCell(5).setCellValue(0);
                row.createCell(6).setCellValue(0);
                row.createCell(7).setCellValue(0);
                row.createCell(8).setCellValue(0);
                row.createCell(9).setCellValue(0);
                row.createCell(10).setCellValue(0);
                row.createCell(11).setCellValue(0);
                row.createCell(12).setCellValue(0);
                row.createCell(13).setCellValue(0);
                row.createCell(14).setCellValue(0);
                row.createCell(15).setCellValue(0);
                row.createCell(16).setCellValue(0);
            }
            for (Cell cell : row) {
                cell.setCellStyle(txtStyle2);
            }
            i++;
        }

        //合计
        HSSFCellStyle txtStyle3 = wb.createCellStyle();
        HSSFFont txtFont3 = wb.createFont();
        txtFont3.setFontName("宋体");
        txtFont3.setFontHeightInPoints((short) 11);
        txtFont3.setBold(true);
        txtStyle3.setFont(txtFont3);
        txtStyle3.setAlignment(HorizontalAlignment.CENTER);
        txtStyle3.setVerticalAlignment(VerticalAlignment.CENTER);
        txtStyle3.setBorderBottom(BorderStyle.THIN);
        txtStyle3.setBorderLeft(BorderStyle.THIN);
        txtStyle3.setBorderTop(BorderStyle.THIN);
        txtStyle3.setBorderRight(BorderStyle.THIN);
        row = sheet.createRow(i);
        Map map = humanResourcesMapper.findTotalNum(years, months,maps);
        row.createCell(0).setCellValue("合计");
        row.createCell(1).setCellValue(map.get("totalWages").toString());
        row.createCell(2).setCellValue(map.get("monthEndNum").toString());
        row.createCell(3).setCellValue(map.get("averageWages").toString());
        row.createCell(4).setCellValue(map.get("lastMonthComparison").toString());
        row.createCell(5).setCellValue(map.get("totalRecruit").toString());
        row.createCell(6).setCellValue(map.get("positionNum").toString());
        row.createCell(7).setCellValue(map.get("averageRecruit").toString());
        row.createCell(8).setCellValue(map.get("totalTrain").toString());
        row.createCell(9).setCellValue(map.get("trainNum").toString());
        row.createCell(10).setCellValue(map.get("averageTrain").toString());
        row.createCell(11).setCellValue(map.get("totalOther").toString());
        row.createCell(12).setCellValue(map.get("otherNum").toString());
        row.createCell(13).setCellValue(map.get("averageOther").toString());
        row.createCell(14).setCellValue(map.get("totalHuman").toString());
        row.createCell(15).setCellValue(map.get("humanNum").toString());
        row.createCell(16).setCellValue(map.get("averageHuman").toString());
        for (Cell cell : row) {
            cell.setCellStyle(txtStyle3);
        }
        sheet.setColumnWidth(0, 20 * 256);
        sheet.setColumnWidth(1, 14 * 256);
        sheet.setColumnWidth(2, 14 * 256);
        sheet.setColumnWidth(3, 14 * 256);
        sheet.setColumnWidth(4, 14 * 256);
        sheet.setColumnWidth(5, 14 * 256);
        sheet.setColumnWidth(6, 14 * 256);
        sheet.setColumnWidth(7, 14 * 256);
        sheet.setColumnWidth(8, 14 * 256);
        sheet.setColumnWidth(9, 14 * 256);
        sheet.setColumnWidth(10, 14 * 256);
        sheet.setColumnWidth(11, 14 * 256);
        sheet.setColumnWidth(12, 14 * 256);
        sheet.setColumnWidth(13, 14 * 256);
        sheet.setColumnWidth(14, 14 * 256);
        sheet.setColumnWidth(15, 14 * 256);
        sheet.setColumnWidth(16, 14 * 256);
    }

    @Override
    public void exportCzlrfy(HSSFWorkbook wb, String years, String months, List<Map<String, Object>> maps) {
        HSSFSheet sheet = wb.createSheet("产值利润费用分析");
        HSSFRow row = sheet.createRow(0);
        sheet.setDefaultRowHeightInPoints(10);
        //合并单元格
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 1, 15));
        HSSFCellStyle txtStyle = wb.createCellStyle();
        HSSFFont txtFont = wb.createFont();
        txtFont.setFontName("宋体");
        txtFont.setFontHeightInPoints((short) 20);
        txtFont.setBold(true);
        txtStyle.setFont(txtFont);
        row.createCell(1).setCellValue("金鹏建设集团  " + years + "  年 " + months + "  月人力资源统计月报表");
        for (Cell cell : row) {
            cell.setCellStyle(txtStyle);
        }

        row = sheet.createRow(1);
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 13));
        HSSFCellStyle txtStyle1 = wb.createCellStyle();
        HSSFFont txtFont1 = wb.createFont();
        txtFont1.setFontName("宋体");
        txtFont1.setFontHeightInPoints((short) 16);
        txtFont1.setBold(true);
        txtStyle1.setFont(txtFont1);
        txtStyle1.setAlignment(HorizontalAlignment.CENTER);
        row.createCell(0).setCellValue("八、产值利润费用分析");
        row.createCell(1);row.createCell(2);row.createCell(3);
        row.createCell(4);row.createCell(5);row.createCell(6);
        row.createCell(7);row.createCell(8);row.createCell(9);
        row.createCell(10);row.createCell(11);row.createCell(12);
        row.createCell(13);
        txtStyle1.setBorderBottom(BorderStyle.THIN);
        txtStyle1.setBorderLeft(BorderStyle.THIN);
        txtStyle1.setBorderTop(BorderStyle.THIN);
        txtStyle1.setBorderRight(BorderStyle.THIN);
        for (Cell cell : row) {
            cell.setCellStyle(txtStyle1);
        }

        row = sheet.createRow(2);
        HSSFCellStyle txtStyle2 = wb.createCellStyle();
        HSSFFont txtFont2 = wb.createFont();
        txtFont2.setFontName("宋体");
        txtFont2.setFontHeightInPoints((short) 11);
        txtStyle2.setFont(txtFont2);
        txtStyle2.setAlignment(HorizontalAlignment.CENTER);
        txtStyle2.setVerticalAlignment(VerticalAlignment.CENTER);
        sheet.addMergedRegion(new CellRangeAddress(2, 3, 0, 0));
        sheet.addMergedRegion(new CellRangeAddress(2, 3, 1, 1));
        sheet.addMergedRegion(new CellRangeAddress(2, 3, 2, 2));
        sheet.addMergedRegion(new CellRangeAddress(2, 3, 3, 3));
        sheet.addMergedRegion(new CellRangeAddress(2, 3, 4, 4));
        sheet.addMergedRegion(new CellRangeAddress(2, 3, 5, 5));
        sheet.addMergedRegion(new CellRangeAddress(2, 3, 6, 6));
        sheet.addMergedRegion(new CellRangeAddress(2, 3, 7, 7));
        sheet.addMergedRegion(new CellRangeAddress(2, 3, 8, 8));
        sheet.addMergedRegion(new CellRangeAddress(2, 3, 9, 9));
        sheet.addMergedRegion(new CellRangeAddress(2, 3, 10, 10));
        sheet.addMergedRegion(new CellRangeAddress(2, 3, 11, 11));
        sheet.addMergedRegion(new CellRangeAddress(2, 3, 12, 12));
        sheet.addMergedRegion(new CellRangeAddress(2, 3, 13, 13));

        row.createCell(0).setCellValue("单位");
        row.createCell(1).setCellValue("月末人数");
        row.createCell(2).setCellValue("当月产值(万)");
        row.createCell(3).setCellValue("当月人均产值(万)");
        row.createCell(4).setCellValue("总产值(万)");
        row.createCell(5).setCellValue("总人均产值(万)");
        row.createCell(6).setCellValue("当月利润(万)");
        row.createCell(7).setCellValue("当月人均利润(万)");
        row.createCell(8).setCellValue("总利润(万)");
        row.createCell(9).setCellValue("总人均利润(万)");
        row.createCell(10).setCellValue("当月费用(万)");
        row.createCell(11).setCellValue("当月人均费用(万)");
        row.createCell(12).setCellValue("总费用(万)");
        row.createCell(13).setCellValue("总人均费用(万)");
        txtStyle2.setBorderBottom(BorderStyle.THIN);
        txtStyle2.setBorderLeft(BorderStyle.THIN);
        txtStyle2.setBorderTop(BorderStyle.THIN);
        txtStyle2.setBorderRight(BorderStyle.THIN);
        for (Cell cell : row) {
            cell.setCellStyle(txtStyle2);
        }

        row = sheet.createRow(3);
        row.createCell(0);row.createCell(1);row.createCell(2);
        row.createCell(3);row.createCell(4);row.createCell(5);
        row.createCell(6);row.createCell(7);row.createCell(8);
        row.createCell(9);row.createCell(10);row.createCell(11);
        row.createCell(12);row.createCell(13);
        for (Cell cell : row) {
            cell.setCellStyle(txtStyle2);
        }

        /**
         * 后端拿取自身权限所拥有的工程公司
         */
        int i = 4;
        Map<String, Object> parameter = new HashMap<String, Object>();
        parameter.put("years", years);
        parameter.put("months", months);
        parameter.put("compList", maps);
        for (Map map : maps) {
            parameter.put("compId", map.get("compId"));
            List<Map<String, Object>> mapList = czlrfyMapper.list(parameter);
            row = sheet.createRow(i);
            if (mapList.size() > 1) {
                Map<String, Object> map1 = mapList.get(0);
                row.createCell(0).setCellValue(map1.get("compName").toString());
                row.createCell(1).setCellValue(map1.get("monthEndNum").toString());
                row.createCell(2).setCellValue(map1.get("thisMonthsOutput").toString());
                row.createCell(3).setCellValue(map1.get("thisMonthsAvgOutput").toString());
                row.createCell(4).setCellValue(map1.get("totalOutput").toString());
                row.createCell(5).setCellValue(map1.get("totalAvgOutput").toString());
                row.createCell(6).setCellValue(map1.get("thisMonthsProfit").toString());
                row.createCell(7).setCellValue(map1.get("thisMonthsAvgProfit").toString());
                row.createCell(8).setCellValue(map1.get("totalProfit").toString());
                row.createCell(9).setCellValue(map1.get("totalAvgProfit").toString());
                row.createCell(10).setCellValue(map1.get("thisMonthsCost").toString());
                row.createCell(11).setCellValue(map1.get("thisMonthsAvgCost").toString());
                row.createCell(12).setCellValue(map1.get("totalCost").toString());
                row.createCell(13).setCellValue(map1.get("totalAvgCost").toString());
            }else {
                row.createCell(0).setCellValue(publicMapper.findRoleName(map.get("compId").toString()));
                row.createCell(1).setCellValue(0);
                row.createCell(2).setCellValue(0);
                row.createCell(3).setCellValue(0);
                row.createCell(4).setCellValue(0);
                row.createCell(5).setCellValue(0);
                row.createCell(6).setCellValue(0);
                row.createCell(7).setCellValue(0);
                row.createCell(8).setCellValue(0);
                row.createCell(9).setCellValue(0);
                row.createCell(10).setCellValue(0);
                row.createCell(11).setCellValue(0);
                row.createCell(12).setCellValue(0);
                row.createCell(13).setCellValue(0);
            }
            for (Cell cell : row) {
                cell.setCellStyle(txtStyle2);
            }
            i++;
        }

        //合计
        HSSFCellStyle txtStyle3 = wb.createCellStyle();
        HSSFFont txtFont3 = wb.createFont();
        txtFont3.setFontName("宋体");
        txtFont3.setFontHeightInPoints((short) 11);
        txtFont3.setBold(true);
        txtStyle3.setFont(txtFont3);
        txtStyle3.setAlignment(HorizontalAlignment.CENTER);
        txtStyle3.setVerticalAlignment(VerticalAlignment.CENTER);
        txtStyle3.setBorderBottom(BorderStyle.THIN);
        txtStyle3.setBorderLeft(BorderStyle.THIN);
        txtStyle3.setBorderTop(BorderStyle.THIN);
        txtStyle3.setBorderRight(BorderStyle.THIN);
        row = sheet.createRow(i);
        Map map = czlrfyMapper.findTotalNum(years, months,maps);
        row.createCell(0).setCellValue("合计");
        row.createCell(1).setCellValue(map.get("monthEndNum").toString());
        row.createCell(2).setCellValue(map.get("thisMonthsOutput").toString());
        row.createCell(3).setCellValue(map.get("thisMonthsAvgOutput").toString());
        row.createCell(4).setCellValue(map.get("totalOutput").toString());
        row.createCell(5).setCellValue(map.get("totalAvgOutput").toString());
        row.createCell(6).setCellValue(map.get("thisMonthsProfit").toString());
        row.createCell(7).setCellValue(map.get("thisMonthsAvgProfit").toString());
        row.createCell(8).setCellValue(map.get("totalProfit").toString());
        row.createCell(9).setCellValue(map.get("totalAvgProfit").toString());
        row.createCell(10).setCellValue(map.get("thisMonthsCost").toString());
        row.createCell(11).setCellValue(map.get("thisMonthsAvgCost").toString());
        row.createCell(12).setCellValue(map.get("totalCost").toString());
        row.createCell(13).setCellValue(map.get("totalAvgCost").toString());
        for (Cell cell : row) {
            cell.setCellStyle(txtStyle3);
        }

        sheet.setColumnWidth(0, 20 * 256);
        sheet.setColumnWidth(1, 14 * 256);
        sheet.setColumnWidth(2, 17 * 256);
        sheet.setColumnWidth(3, 19 * 256);
        sheet.setColumnWidth(4, 17 * 256);
        sheet.setColumnWidth(5, 17 * 256);
        sheet.setColumnWidth(6, 17 * 256);
        sheet.setColumnWidth(7, 19 * 256);
        sheet.setColumnWidth(8, 17 * 256);
        sheet.setColumnWidth(9, 17 * 256);
        sheet.setColumnWidth(10, 17 * 256);
        sheet.setColumnWidth(11, 19 * 256);
        sheet.setColumnWidth(12, 17 * 256);
        sheet.setColumnWidth(13, 17 * 256);
    }

    @Override
    public void exportEmpCertificate(HSSFWorkbook wb, String years, String months, List<Map<String, Object>> maps) {
        HSSFSheet sheet = wb.createSheet("员工持证情况分析");
        HSSFRow row = sheet.createRow(0);
        sheet.setDefaultRowHeightInPoints(10);
        //合并单元格
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 1, 15));
        HSSFCellStyle txtStyle = wb.createCellStyle();
        HSSFFont txtFont = wb.createFont();
        txtFont.setFontName("宋体");
        txtFont.setFontHeightInPoints((short) 20);
        txtFont.setBold(true);
        txtStyle.setFont(txtFont);
        row.createCell(1).setCellValue("金鹏建设集团  " + years + "  年 " + months + "  月人力资源统计月报表");
        for (Cell cell : row) {
            cell.setCellStyle(txtStyle);
        }

        row = sheet.createRow(1);
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 12));
        HSSFCellStyle txtStyle1 = wb.createCellStyle();
        HSSFFont txtFont1 = wb.createFont();
        txtFont1.setFontName("宋体");
        txtFont1.setFontHeightInPoints((short) 16);
        txtFont1.setBold(true);
        txtStyle1.setFont(txtFont1);
        txtStyle1.setAlignment(HorizontalAlignment.CENTER);
        row.createCell(0).setCellValue("九、员工持证情况分析");
        row.createCell(1);row.createCell(2);row.createCell(3);
        row.createCell(4);row.createCell(5);row.createCell(6);
        row.createCell(7);row.createCell(8);row.createCell(9);
        row.createCell(10);row.createCell(11);row.createCell(12);
        txtStyle1.setBorderBottom(BorderStyle.THIN);
        txtStyle1.setBorderLeft(BorderStyle.THIN);
        txtStyle1.setBorderTop(BorderStyle.THIN);
        txtStyle1.setBorderRight(BorderStyle.THIN);
        for (Cell cell : row) {
            cell.setCellStyle(txtStyle1);
        }

        row = sheet.createRow(2);
        HSSFCellStyle txtStyle2 = wb.createCellStyle();
        HSSFFont txtFont2 = wb.createFont();
        txtFont2.setFontName("宋体");
        txtFont2.setFontHeightInPoints((short) 11);
        txtStyle2.setFont(txtFont2);
        txtStyle2.setAlignment(HorizontalAlignment.CENTER);
        txtStyle2.setVerticalAlignment(VerticalAlignment.CENTER);
        sheet.addMergedRegion(new CellRangeAddress(2, 3, 0, 0));
        sheet.addMergedRegion(new CellRangeAddress(2, 2, 1, 6));
        sheet.addMergedRegion(new CellRangeAddress(2, 2, 7, 8));
        sheet.addMergedRegion(new CellRangeAddress(2, 2, 9, 12));
        row.createCell(0).setCellValue("单位");
        row.createCell(1).setCellValue("建筑工程类");
        row.createCell(2);row.createCell(3);row.createCell(4);
        row.createCell(5);row.createCell(6);
        row.createCell(7).setCellValue("人资类");
        row.createCell(8);
        row.createCell(9).setCellValue("财务类");
        row.createCell(10);row.createCell(11);row.createCell(12);
        txtStyle2.setBorderBottom(BorderStyle.THIN);
        txtStyle2.setBorderLeft(BorderStyle.THIN);
        txtStyle2.setBorderTop(BorderStyle.THIN);
        txtStyle2.setBorderRight(BorderStyle.THIN);
        for (Cell cell : row) {
            cell.setCellStyle(txtStyle2);
        }

        row = sheet.createRow(3);
        row.createCell(0);
        row.createCell(1).setCellValue("一建");
        row.createCell(2).setCellValue("二建");
        row.createCell(3).setCellValue("造价师");
        row.createCell(4).setCellValue("安全A证");
        row.createCell(5).setCellValue("安全B证");
        row.createCell(6).setCellValue("安全C证");
        row.createCell(7).setCellValue("人力资源一级");
        row.createCell(8).setCellValue("人力资源二级");
        row.createCell(9).setCellValue("注册会计师");
        row.createCell(10).setCellValue("高级会计师");
        row.createCell(11).setCellValue("中级会计师");
        row.createCell(12).setCellValue("助理会计师");
        for (Cell cell : row) {
            cell.setCellStyle(txtStyle2);
        }

        /**
         * 后端拿取自身权限所拥有的工程公司
         */
        int i = 4;
        Map<String, Object> parameter = new HashMap<String, Object>();
        parameter.put("years", years);
        parameter.put("months", months);
        parameter.put("compList", maps);
        for (Map map : maps) {
            parameter.put("compId", map.get("compId"));
            List<Map<String, Object>> mapList = empCertificateMapper.list(parameter);
            row = sheet.createRow(i);
            if (mapList.size() > 1) {
                Map<String, Object> map1 = mapList.get(0);
                row.createCell(0).setCellValue(map1.get("compName").toString());
                row.createCell(1).setCellValue(map1.get("firstConstructionNum").toString());
                row.createCell(2).setCellValue(map1.get("secondConstructionNum").toString());
                row.createCell(3).setCellValue(map1.get("costEngineerNum").toString());
                row.createCell(4).setCellValue(map1.get("securityANum").toString());
                row.createCell(5).setCellValue(map1.get("securityBNum").toString());
                row.createCell(6).setCellValue(map1.get("securityCNum").toString());
                row.createCell(7).setCellValue(map1.get("humanResourcesOneNum").toString());
                row.createCell(8).setCellValue(map1.get("humanResourcesTowNum").toString());
                row.createCell(9).setCellValue(map1.get("registerAccountingNum").toString());
                row.createCell(10).setCellValue(map1.get("seniorAccountingNum").toString());
                row.createCell(11).setCellValue(map1.get("intermediateAccountingNum").toString());
                row.createCell(12).setCellValue(map1.get("assistantAccountingNum").toString());
            }else {
                row.createCell(0).setCellValue(publicMapper.findRoleName(map.get("compId").toString()));
                row.createCell(1).setCellValue(0);
                row.createCell(2).setCellValue(0);
                row.createCell(3).setCellValue(0);
                row.createCell(4).setCellValue(0);
                row.createCell(5).setCellValue(0);
                row.createCell(6).setCellValue(0);
                row.createCell(7).setCellValue(0);
                row.createCell(8).setCellValue(0);
                row.createCell(9).setCellValue(0);
                row.createCell(10).setCellValue(0);
                row.createCell(11).setCellValue(0);
                row.createCell(12).setCellValue(0);
            }
            for (Cell cell : row) {
                cell.setCellStyle(txtStyle2);
            }
            i++;
        }

        //合计
        HSSFCellStyle txtStyle3 = wb.createCellStyle();
        HSSFFont txtFont3 = wb.createFont();
        txtFont3.setFontName("宋体");
        txtFont3.setFontHeightInPoints((short) 11);
        txtFont3.setBold(true);
        txtStyle3.setFont(txtFont3);
        txtStyle3.setAlignment(HorizontalAlignment.CENTER);
        txtStyle3.setVerticalAlignment(VerticalAlignment.CENTER);
        txtStyle3.setBorderBottom(BorderStyle.THIN);
        txtStyle3.setBorderLeft(BorderStyle.THIN);
        txtStyle3.setBorderTop(BorderStyle.THIN);
        txtStyle3.setBorderRight(BorderStyle.THIN);
        row = sheet.createRow(i);
        Map map = empCertificateMapper.findTotalNum(years, months,maps);
        row.createCell(0).setCellValue("合计");
        row.createCell(1).setCellValue(map.get("firstConstructionNum").toString());
        row.createCell(2).setCellValue(map.get("secondConstructionNum").toString());
        row.createCell(3).setCellValue(map.get("costEngineerNum").toString());
        row.createCell(4).setCellValue(map.get("securityANum").toString());
        row.createCell(5).setCellValue(map.get("securityBNum").toString());
        row.createCell(6).setCellValue(map.get("securityCNum").toString());
        row.createCell(7).setCellValue(map.get("humanResourcesOneNum").toString());
        row.createCell(8).setCellValue(map.get("humanResourcesTowNum").toString());
        row.createCell(9).setCellValue(map.get("registerAccountingNum").toString());
        row.createCell(10).setCellValue(map.get("seniorAccountingNum").toString());
        row.createCell(11).setCellValue(map.get("intermediateAccountingNum").toString());
        row.createCell(12).setCellValue(map.get("assistantAccountingNum").toString());
        for (Cell cell : row) {
            cell.setCellStyle(txtStyle3);
        }

        sheet.setColumnWidth(0, 20 * 256);
        sheet.setColumnWidth(1, 14 * 256);
        sheet.setColumnWidth(2, 14 * 256);
        sheet.setColumnWidth(3, 14 * 256);
        sheet.setColumnWidth(4, 14 * 256);
        sheet.setColumnWidth(5, 14 * 256);
        sheet.setColumnWidth(6, 14 * 256);
        sheet.setColumnWidth(7, 14 * 256);
        sheet.setColumnWidth(8, 14 * 256);
        sheet.setColumnWidth(9, 14 * 256);
        sheet.setColumnWidth(10, 14 * 256);
        sheet.setColumnWidth(11, 14 * 256);
        sheet.setColumnWidth(12, 14 * 256);
    }
}
